package com.bgh.dynobot;
import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DynoDbAdapter {

    public static final String KEY_ROWID = "_id";
    public static final String KEY_RUN_ID = "run_id";
    public static final String KEY_RUN_TIME = "run_time";
    public static final String KEY_DISTANCE = "distance";
    public static final String KEY_ACCELERATION = "acceleration";
    public static final String KEY_VELOCITY = "velocity";
    public static final String KEY_VEHICLE_ID = "vehicle_id";
    public static final String KEY_RUN_DATE = "run_date";
    public static final String KEY_NAME = "name";
    public static final String KEY_DESC = "desc";
    public static final String KEY_WEIGHT = "weight";
    public static final String KEY_WEIGHT_UNIT = "weight_unit";
    public static final String KEY_PIC_URI = "pic_uri";
    
    private static final String TAG = "DynoDbAdapter";
    private DatabaseHelper DBHelper;
    private SQLiteDatabase DB;
    
    /**
     * Database creation sql statement
     */
    private static final String TIMES_TABLE_CREATE =
    	"create table times (_id integer primary key autoincrement, " +
        "run_id integer not null, " +
        " run_time real not null, distance real not null, acceleration real not null, " +
        "velocity real not null);";
    
    private static final String RUNS_TABLE_CREATE =
    	"create table runs (" +
        " run_id integer primary key autoincrement, vehicle_id integer not null, " +
        " run_date text not null);";
    
    private static final String VEHICLES_TABLE_CREATE =
        "create table vehicles (vehicle_id integer primary key autoincrement, " +
                "name text not null, desc text, " +
                " weight integer, weight_unit text, pic_uri text);";

    private static final String DATABASE_NAME = "dyno_db";
    private static final String TIMES_TABLE = "times";
    private static final String RUNS_TABLE = "runs";
    private static final String VEHICLES_TABLE = "vehicles";
    private static final int DATABASE_VERSION = 2;

    private final Context mCtx;

    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {

            db.execSQL(TIMES_TABLE_CREATE);
            db.execSQL(RUNS_TABLE_CREATE);
            db.execSQL(VEHICLES_TABLE_CREATE);
            
            ContentValues initialValues = new ContentValues();
            initialValues.put(KEY_NAME, "Default vehicle");
            initialValues.put(KEY_DESC, "Use for testing or once off timed runs");
            initialValues.put(KEY_WEIGHT, 0);
            initialValues.put(KEY_WEIGHT_UNIT, "kgs");
            initialValues.put(KEY_PIC_URI, "");
            
            db.insert("vehicles", null, initialValues);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS notes");
            onCreate(db);
        }
    }

    public DynoDbAdapter(Context ctx) {
        this.mCtx = ctx;
    }

    public DynoDbAdapter open() throws SQLException {
        DBHelper = new DatabaseHelper(mCtx);
        DB = DBHelper.getWritableDatabase();
        return this;
    }
    
    public void close() {
        DBHelper.close();
    }
    
    public void insertTime(int run_id, ArrayList<double[]> runDataArrayList) {

    	String sql = "insert into " + TIMES_TABLE + "(" + KEY_RUN_ID + "," +
    	KEY_RUN_TIME + "," + KEY_DISTANCE + "," + KEY_ACCELERATION + "," + 
    	KEY_VELOCITY+ ")"; 
    	
    	for(int i = 0; i < runDataArrayList.size(); i++){
    	//for(int i = 0; i < 3; i++){
        	double[] runData = runDataArrayList.get(i); 
        	if (i == 0){
        		sql = sql + " select " + run_id + ",";
        	}else{
        		sql = sql + " union all select " + run_id + "," ;
        	}
        	sql = sql + runData[0] + "," + runData[1] + "," + 	runData[2] + "," + 	runData[3];
        }
    	DB.execSQL(sql);
    }

    public long insertRun(int vehicle_id, String run_date) {
        ContentValues insertValues = new ContentValues();
        insertValues.put(KEY_VEHICLE_ID, vehicle_id);
        insertValues.put(KEY_RUN_DATE, run_date);
        
        return DB.insert(RUNS_TABLE, null, insertValues);
    }

    public boolean deleteRun(int run_id) {
        return DB.delete(RUNS_TABLE, KEY_RUN_ID + " = " + run_id, null) > 0;
    }
    
    public Cursor fetchLastRunID() throws SQLException {
        Cursor cursor =
        	DB.rawQuery("select last_insert_rowid() from runs", null);
        if (cursor != null) {
            cursor.moveToFirst();
        }
        return cursor;
    }
    
    public Cursor fetchTimes(int runID) throws SQLException {
        Cursor cursor =
                DB.query(true, TIMES_TABLE, new String[] {KEY_RUN_TIME,
                		KEY_DISTANCE, KEY_VELOCITY},  
                        KEY_RUN_ID + " = " + runID, null,
                        null, null, "_id asc", null);
        if (cursor != null) {
            cursor.moveToFirst();
        }
        return cursor;
    }
    
    public Cursor fetchRunID(int vehicleID, String runDate) throws SQLException {
        /*Cursor cursor =
                DB.query(true, RUNS_TABLE, new String[] {KEY_RUN_ID},  
                        KEY_VEHICLE_ID + " = " + vehicleID + " and " +
                        KEY_RUN_DATE + " = \'" + runDate + "\'", null,
                        null, null, null, null);*/
    	if(runDate.equals("Last run")){
    		runDate = "(select max(" + KEY_RUN_DATE + ") from " + RUNS_TABLE +
    			" where " + KEY_VEHICLE_ID + " = " + vehicleID + ")";
    	}else{
    		runDate = "\'" + runDate + "\'";
    	}
    	
    	Cursor cursor =
        	DB.rawQuery("select " + KEY_RUN_ID + " from " + RUNS_TABLE + " where " +
        			KEY_VEHICLE_ID + " = " + vehicleID + " and " +
        			KEY_RUN_DATE + " = " + runDate, null);
        if (cursor != null) {
            cursor.moveToFirst();
        }
        return cursor;
    }
    
    public long insertVehicle(String name, String desc, int weight, String weight_unit, String pic_uri) {
        ContentValues insertValues = new ContentValues();
        insertValues.put(KEY_NAME, name);
        insertValues.put(KEY_DESC, desc);
        insertValues.put(KEY_WEIGHT, weight);
        insertValues.put(KEY_WEIGHT_UNIT, weight_unit);
        insertValues.put(KEY_PIC_URI, pic_uri);
        
        return DB.insert(VEHICLES_TABLE, null, insertValues);
    }

    public boolean deleteVehicle(String vehicle_name) {
        return DB.delete(VEHICLES_TABLE, KEY_NAME + " = \"" + vehicle_name + "\"", null) > 0;
    }
    
    public Cursor fetchVehicle(String name) throws SQLException {
        Cursor cursor =
                DB.query(true, VEHICLES_TABLE, new String[] {KEY_VEHICLE_ID,
                		KEY_NAME, KEY_DESC, KEY_WEIGHT, KEY_WEIGHT_UNIT, KEY_PIC_URI},  
                        KEY_NAME + " = \"" + name + "\"", null,
                        null, null, null, null);
        if (cursor != null) {
            cursor.moveToFirst();
        }
        return cursor;
    }
    
    public Cursor fetchAllVehicles() throws SQLException {
        Cursor cursor =
        	DB.query(true, VEHICLES_TABLE, new String[] {KEY_VEHICLE_ID,
            		KEY_NAME, KEY_DESC, KEY_WEIGHT, KEY_WEIGHT_UNIT, KEY_PIC_URI},  
                        null, null,
                        null, null, null, null);
        if (cursor != null) {
            cursor.moveToFirst();
        }
        return cursor;
    }
    
    public Cursor fetchAllRuns(int vehicleID) throws SQLException {
        Cursor cursor =
        	DB.query(true, RUNS_TABLE, new String[] {KEY_VEHICLE_ID,KEY_RUN_DATE},  
            		KEY_VEHICLE_ID + " = \"" + vehicleID + "\"", null,
                        null, null, null, null);
        if (cursor != null) {
            cursor.moveToFirst();
        }
        return cursor;
    }
    
}
